import ast
import re
from typing import Optional
import  mysql as mq
import akshare as ak

import jsl.jsldetail as jsldetail

# 查询贴现率
select_bond_rate_sql = "select * from bond_rate where date = %s order by date limit 1 "
# 数据库插入贴现率
insert_bond_rate_sql = "insert into bond_rate(date,bond_rate_2,bond_rate_5,bond_rate_10,bond_rate_30) values(%s,%s,%s,%s,%s)"


def get_bond_rate_10(conn, cursor, useday, ) -> Optional[float]:
    """
    数据库获取国债收益率
    @param useday:
    @return:
    """
    row_count = cursor.execute(select_bond_rate_sql, (useday))
    # 获取查询结果
    if row_count > 0:
        result = cursor.fetchone()  # 获取第一行结果
        dis_rate = result['bond_rate_10']  # 提取第一列的值（bond_rate_10）
        bond_rate_10 = float(dis_rate)
        print(f"数据库成功获取贴现率：{dis_rate}%")
        return bond_rate_10
    else:
        print("数据库未找到符合条件的数据，调用AKShare接口查询,保存数据库")
        bond_rate_10 = get_bond_rate_today(useday, conn, cursor)
        return bond_rate_10


def get_bond_rate_today(formatted_date, conn, cursor):
    bond_df = ak.bond_zh_us_rate(start_date=formatted_date)
    if bond_df.empty:
        sql = "select * from bond_rate where 1=1 order by date limit 1"
        row_count = cursor.execute(sql)
        # 获取查询结果
        if row_count > 0:
            result = cursor.fetchone()  # 获取第一行结果
            dis_rate = result['bond_rate_10']  # 提取第一列的值（bond_rate_10）
            bond_rate_10 = float(dis_rate)
            print(f"接口获取数据为空，返回数据库最新值:{dis_rate}%")
            return bond_rate_10
        else:
            print("数据库没有最新值,没有获取到贴现率,返回默认值 2.0")
            return 2.0
    else:
        data = bond_df.iloc[0]
        print(f"AKShare接口获取到贴现率{data.中国国债收益率10年},保存到数据库")
        cursor.execute(insert_bond_rate_sql, (formatted_date, data.中国国债收益率2年,
                                              data.中国国债收益率5年, data.中国国债收益率10年, data.中国国债收益率30年))
        conn.commit()  # 提交事务
        return data.中国国债收益率10年


# 获取可转债前缀
def getFullBondId(bond_id):
    # 去掉前两位
    if bond_id.startswith("11"):
        return "sh"
    if bond_id.startswith("12"):
        return "sz"
    return ''


# 插入数据库可转债票面利率
cb_rate_inset_sql = "insert into cb_rate(bond_id,rate_list,expire_value,valid,prefix) values(%s,%s,%s,'1',%s)"
# 查询数据库票面利率
rate_list_select_sql = "select * from cb_rate where bond_id=%s and valid='1' and prefix =%s"


def get_rate_dic_bond_id(conn, cursor, bond_id):
    """
    h获取可转债票面利率
    @param bond_id:
    @param conn:
    @param cursor:
    @return:
    """

    bond_prefix = getFullBondId(str(bond_id))  # 去掉前两位
    row_count = cursor.execute(rate_list_select_sql, (bond_id, bond_prefix))
    expire_value = 105
    if row_count > 0:
        result = cursor.fetchone()  # 获取第一行结果
        rate_str = result['rate_list']
        expire_value = result['expire_value']
        rate_dic = ast.literal_eval(rate_str)  # 将字符串转换为字典
    else:
        print(f"数据库没有查询到可转债({bond_id})票面利息,调用AKShare接口可转债")
        rate_dic = insert_rate_list(str(bond_id), bond_prefix, conn, cursor)

    return float(expire_value), rate_dic


def insert_rate_list(bond_id, bond_prefix, conn, cursor) -> dict:
    """
    获取可转债票面利率
    @param bond_id:
    @param bond_prefix:
    @param conn:
    @param cursor:
    @return:
    """

    print(f"获取可转债信息失败，bond_id={bond_id},调用集思录接口")
    try:
        bond_detail = jsldetail.BondDetail()
        respose_txt = bond_detail.get_bond_detail(bond_id)
        expire_value, rate_dic = bond_detail.bond_detail_mapper.get_cb_rates_dic_jsl(respose_txt)
        print(f"集思录获取成功")
    except Exception as e:
        print(f"调用集思录接口失败，bond_id={bond_id},错误信息：{str(e)}")
        return {}

    rate_str = str(rate_dic)
    if len(rate_str) > 0:
        cursor.execute(cb_rate_inset_sql, (bond_id, rate_str, expire_value, bond_prefix))
        conn.commit()  # 提交事务
    return rate_dic


# try:
#     bond_df = ak.bond_cb_profile_sina(symbol=bond_prefix + bond_id)
#     cols = bond_df.values[11][1]
#     print(cols)
#     # 匹配所有数字（含小数），前面跟着“年”
#     rates = re.findall(r'(\d+\.?\d*)%', cols)
#     # 转换为浮点数
#     rates = [float(r) for r in rates]
#     rate_dic = {}
#     expire_value = None
#     for i in range(len(rates)):
#         rate_dic[i + 1] = rates[i]
# except Exception as e:
#     print(f"获取可转债信息失败，bond_id={bond_id},调用集思录接口")
#     try:
#         bond_detail = jsldetail.BondDetail()
#         respose_txt = bond_detail.get_bond_detail(bond_id)
#         expire_value, rate_dic = bond_detail.bond_detail_mapper.get_cb_rates_dic_jsl(respose_txt)
#         print(f"集思录获取成功")
#     except Exception as e:
#         print(f"调用集思录接口失败，bond_id={bond_id},错误信息：{str(e)}")
#         return {}
# rate_str = str(rate_dic)
# if len(rate_str) > 0:
#     cursor.execute(cb_rate_inset_sql, (bond_id, rate_str, expire_value, bond_prefix))
#     conn.commit()  # 提交事务
# return rate_dic

if __name__ == "__main__":
    conn = mq.get_meysql_conn()
    cursor = conn.cursor()
    get_bond_rate_today("20251019", conn, cursor)
